﻿
Partial Class WebForm_QueryHonorCert
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            RaiseEvent Loaded(Me, New EventArgs)

        End If

    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub


    Protected Sub gv_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gv.SelectedIndexChanged
        RaiseEvent SelectedIndexChanged(Me, New EventArgs)
    End Sub

 
    Protected Sub btnQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnQuery.Click
        RaiseEvent btnQueryClick(Me, New EventArgs)
    End Sub


    Protected Sub gv_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gv.Sorting
        Me.BindGV()
    End Sub
End Class

Partial Class WebForm_QueryHonorCert



    Public Sub BindGV()
        Me.BindGV(Me.gv)
    End Sub

    Private Sub BindGV(ByVal gv As GridView)
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As Data.DataTable

        sql.AppendFormat(" select v.VolunteerID,v.VolunteerName ")
        sql.AppendFormat(",HC.HonorCertID ")
        sql.AppendFormat(",HC.HonorCertCode ")
        sql.AppendFormat(",HC.IssueDate ")
        sql.AppendFormat(",HC.EndDate,HC.Comment ")
        sql.AppendFormat(" from HonorCert HC ")
        sql.AppendFormat(" inner join VolunteerDistinctView v on v.VolunteerID = HC.VolunteerID ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on v.VolunteerID=oo.VolunteerID  ")

        sql.AppendFormat("where 1=1 ")
        sql.AppendFormat(GetWhereSQL())
        sql.AppendFormat(Me.gv.OrderBySQL)
        ' MyObj.WriteErrorLog(sql.ToString())
        dt = dat.GetData(sql)

        gv.DataSource = dt
        gv.DataBind()
    End Sub

    Private Sub BindGV2(ByVal gv As GridView)
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim dt As Data.DataTable

        sql.AppendFormat(" select v.* ")
        sql.AppendFormat(",Case v.Gender when 'F' then '女' else '男' end vGender ")
        sql.AppendFormat(GetDate("v.Birthday", "vBirthday"))
        sql.AppendFormat(",vb.VBookID,R1.RegionName")
        sql.AppendFormat(",2 as VBookTypeID, '050' as SerialGroupID, vb.SerialNumber, vb.IssueDate ")
        sql.AppendFormat(",'' as Picture,'V' as GetVBook,'' as ChangeVBook ")
        sql.AppendFormat(GetDate("tgb.StartDate", "tgbStartDate"))
        sql.AppendFormat(", tgb.Org as trbOrg, tgs.Org as trsOrg ")
        sql.AppendFormat(GetDate("tgs.StartDate", "tgsStartDate"))
        sql.AppendFormat(" from VolunteerDistinctView v ")
        sql.AppendFormat(" inner join VBookView vb on v.VolunteerID = vb.VolunteerID ")
        sql.AppendFormat(" Left join Region R1 on v.VillageID = R1.RegionID ")
        sql.AppendFormat(" inner join trainingregister trb on v.VolunteerID = trb.VolunteerID ")
        sql.AppendFormat(" inner join trainingcoursegroup tgb on trb.GroupID = tgb.ID and tgb.GroupType='B' ")
        sql.AppendFormat(" inner join trainingregister trs on v.VolunteerID = trs.VolunteerID ")
        sql.AppendFormat(" inner join trainingcoursegroup tgs on trs.GroupID = tgs.ID and tgs.GroupType='S' ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on v.VolunteerID=oo.VolunteerID  ")
        sql.AppendFormat(" where v.Active = '{0}' ", My.VolunteerStatus.VBooked.ToString())
        sql.AppendFormat(GetWhereSQL())

        dt = dat.GetData(sql)
        gv.DataSource = dt
        gv.DataBind()
    End Sub



    Private Function GetWhereSQL() As String
        Dim sql As New MyStringBuilder
        If Me.VolunteerQueryControl1.VolunteerId = "" Then

            If Me.VolunteerQueryControl1.OrganizationID <> "0" Then
                sql.AppendFormat("and oo.DirectUOrganizationID = '{0}'   ", Me.VolunteerQueryControl1.OrganizationID)
            End If

            If Me.VolunteerQueryControl1.VOrganizationID <> "0" Then
                sql.AppendFormat("AND oo.VOrganizationID = {0}  ", Me.VolunteerQueryControl1.VOrganizationID)
            End If
            sql.AppendFormat(" and (v.VolunteerName like '%{0}%' or ''='{0}')", Me.VolunteerQueryControl1.VolunteerName)


           
        Else
            sql.AppendFormat(" and (v.VolunteerID='{0}' or ''='{0}')", Me.VolunteerQueryControl1.VolunteerId)

        End If
        ' If Me.txtEffectiveStartDate.Text <> DateTime.Now.ToString("yyyy/MM/dd") AndAlso Me.txtEffectiveEndDate.Text <> DateTime.Now.ToString("yyyy/MM/dd") Then
        sql.AppendFormat(" and EndDate between '{0:yyyy/MM/dd}' and '{1:yyyy/MM/dd}'", Me.txtEffectiveStartDate.Value, Me.txtEffectiveEndDate.Value.AddDays(1))

        'End If
        Return sql.ToString()
    End Function

    Private Function GetDate(ByVal oldName As String, ByVal newName As String) As String
        Dim sql As New MyStringBuilder

        sql.AppendFormat(",Case " + oldName + " when null then null ")
        sql.AppendFormat(" else Cast((Year(" + oldName + ")-1911) as varchar)+'.' ")
        sql.AppendFormat(" +Right('0'+Cast(month(" + oldName + ") as varchar),2)+'.' ")
        sql.AppendFormat(" +Right('0'+Cast(day(" + oldName + ") as varchar),2) end " + newName + " ")

        Return sql.ToString()
    End Function


    Private ReadOnly Property VolunteerID() As String
        Get
            Return Me.VolunteerQueryControl1.VolunteerId
        End Get
    End Property

    Private ReadOnly Property VolunteerName() As String
        Get
            Return Me.VolunteerQueryControl1.VolunteerName
        End Get
    End Property

    Public ReadOnly Property SelectedVolunteerID() As String
        Get
            If Me.gv.SelectedRow Is Nothing Then
                Return Me.gv.Rows(0).Cells(0).Text
            Else
                Return Me.gv.SelectedRow.Cells(0).Text
            End If
        End Get
    End Property

    Public ReadOnly Property SelectedHonorCertID() As String
        Get
            If Me.gv.SelectedRow Is Nothing Then
                Return TryCast(Me.gv.Rows(0).FindControl("lblHonorCertID"), Label).Text
            Else
                Return TryCast(Me.gv.SelectedRow.FindControl("lblHonorCertID"), Label).Text
            End If
        End Get
    End Property

    Public Event Loaded(ByVal source As Object, ByVal e As EventArgs)
    Public Event SelectedIndexChanged(ByVal source As Object, ByVal e As EventArgs)
    Public Event btnQueryClick(ByVal sender As Object, ByVal e As System.EventArgs)
End Class